{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date,datetime\n",
    "import os\n",
    "\n",
    "\n",
    "target_path = r'E:\\工资表\\工资表\\xlsx\\测试'\n",
    "missing_values = [\"n/a\", \"na\", \"——\"]\n",
    "#遍历当前文件内容\n",
    "for root, dirs, files in os.walk(target_path):\n",
    "    for file in files:\n",
    "        file_name = os.path.join(root, file)\n",
    "        f = pd.ExcelFile(file_name)\n",
    "        #获取文件名称\n",
    "        for i in f.sheet_names:\n",
    "            print(i)\n",
    "            head_df = pd.read_excel(file_name, na_values=missing_values,sheet_name = i)\n",
    "            mydf = pd.read_excel(file_name, na_values=missing_values, header=1,sheet_name = i)\n",
    "            heads = head_df.columns\n",
    "            # print(heads)\n",
    "            #确定内容额外字段的内容\n",
    "            head_arr = []\n",
    "            data_source = ''  # 数据来源\n",
    "            company_name = ''  # 公司名称\n",
    "            salary_grant_date = ''  # 工资发放时间\n",
    "            salary_date = ''  # 工资时间\n",
    "            def convert_to_date(date_str):\n",
    "                parts = date_str.split(\".\")\n",
    "                year = int(parts[0])\n",
    "                month = int(parts[1])\n",
    "                day = int(parts[2])\n",
    "                date_obj = date(year, month, day)\n",
    "                return date_obj\n",
    "            for head in heads:\n",
    "                # print(type(head))\n",
    "                if (head.find(\"公司\") != -1):\n",
    "                    company_name = head.split(\"公司\")[0] + \"公司\"\n",
    "                    salary_date = head.split(\"公司\")[1]\n",
    "                if (head.find(\"日期\") != -1):\n",
    "                    date_str = head.split(\"：\")[1]\n",
    "                    salary_grant_date = convert_to_date(date_str)\n",
    "           \n",
    "            # mydf.rename(社保扣除项=基本养老保险费)\n",
    "            #不合法字段替换\n",
    "            mydf = mydf.rename(columns={'社保扣除项': '基本养老保险费'})\n",
    "            mydf = mydf.rename(columns={'Unnamed: 14': '基本医疗保险费'})\n",
    "            mydf = mydf.rename(columns={'Unnamed: 15': '失业保险费'})\n",
    "            mydf = mydf.rename(columns={'Unnamed: 16': '社保个人扣款合计'})\n",
    "            mydf['签名'] = mydf['签名'].fillna('')\n",
    "            mydf = mydf.fillna(0)\n",
    "            mydf = mydf.drop(index=0)\n",
    "            # 添加字段以及内容\n",
    "            mydf['数据来源'] = data_source\n",
    "            mydf['公司名称'] = company_name\n",
    "            mydf['工资发放时间'] = salary_grant_date\n",
    "            mydf['工资时间'] = salary_date\n",
    "            mydf['序号'] = mydf['序号'].replace('合计',0)\n",
    "            #display(mydf)\n",
    "\n",
    "\n",
    "            # mydf = mydf.loc[1:]\n",
    "            mydf['序号'] = mydf['序号'].astype(int)\n",
    "            mydf.loc[mydf['序号'] > 0].to_csv(\"测试工资输出表.csv\", index=False)\n",
    "            display(mydf.loc[mydf['序号'] > 0])\n",
    "\n",
    "            #劵投名单\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date,datetime,time\n",
    "import os\n",
    "file_name = '（韩）4月工资表.xlsx'\n",
    "f = pd.ExcelFile(file_name)\n",
    "        #获取文件名称\n",
    "def convert_to_date(date_str):\n",
    "    year = int(date_str.split(\"年\")[0])\n",
    "    month = int(date_str.split(\"月\")[0].split(\"年\")[1])\n",
    "    day = int(date_str.split(\"日\")[0].split(\"月\")[1])\n",
    "    print(year)\n",
    "    print(month)\n",
    "    print(day)\n",
    "    return date(year, month, day)\n",
    "data_source = ''  # 数据来源\n",
    "company_name = ''  # 公司名称\n",
    "salary_grant_date = ''  # 工资发放时间\n",
    "salary_date = ''  # 工资时间\n",
    "\n",
    "for name in f.sheet_names:\n",
    "    print(name)\n",
    "    if name == \"券投\":\n",
    "        jtdf = pd.read_excel(file_name,sheet_name=name)\n",
    "        mydf = pd.read_excel(file_name, na_values=missing_values, header=2,sheet_name = name)\n",
    "        heads = head_df.columns\n",
    "        head = jtdf.columns.values[0]\n",
    "        name = head.split(\"券投交易中心\")[1]\n",
    "        grant_salary_str = jtdf.iloc[:,0].values[0].split(\"：\")[1]\n",
    "        company_name = '券投交易中心'\n",
    "        salary_grant_date = head\n",
    "        salary_date = convert_to_date(grant_salary_str)\n",
    "        # 添加字段以及内容\n",
    "        mydf['数据来源'] = data_source\n",
    "        mydf['公司名称'] = company_name\n",
    "        mydf['工资发放时间'] = salary_grant_date\n",
    "        mydf['工资时间'] = salary_date\n",
    "        mydf['签名'] = mydf['签名'].fillna('')\n",
    "        mydf = mydf.fillna(0)\n",
    "        display(mydf.loc[mydf['序号'] > 0])\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date,datetime,time\n",
    "import os\n",
    "file_name = '（韩）4月工资表.xlsx'\n",
    "f = pd.ExcelFile(file_name)\n",
    "        #获取文件名称\n",
    "def convert_to_date(date_str):\n",
    "    year = int(date_str.split(\"年\")[0])\n",
    "    month = int(date_str.split(\"月\")[0].split(\"年\")[1])\n",
    "    day = int(date_str.split(\"日\")[0].split(\"月\")[1])\n",
    "    print(year)\n",
    "    print(month)\n",
    "    print(day)\n",
    "    return date(year, month, day)\n",
    "\n",
    "\n",
    "data_source = ''  # 数据来源\n",
    "company_name = ''  # 公司名称\n",
    "salary_grant_date = ''  # 工资发放时间\n",
    "salary_date = ''  # 工资时间\n",
    "\n",
    "for name in f.sheet_names:\n",
    "    print(name)\n",
    "    if name == \"外聘人员\":\n",
    "        jtdf = pd.read_excel(file_name,sheet_name=name)\n",
    "        mydf = pd.read_excel(file_name, na_values=missing_values, header=1,sheet_name = name)\n",
    "        heads = head_df.columns\n",
    "        head = jtdf.columns.values[0]\n",
    "        name = head.split(\"外聘人员\")[1]\n",
    "        company_name = '外聘人员'\n",
    "        salary_grant_date = head\n",
    "        mydf['数据来源'] = data_source\n",
    "        mydf['公司名称'] = company_name\n",
    "        mydf['工资发放时间'] = salary_grant_date\n",
    "        mydf['工资时间'] = ''\n",
    "        mydf = mydf.fillna(0)\n",
    "        display(mydf.loc[mydf['姓名'] != 0])\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date, datetime\n",
    "import os\n",
    "\n",
    "target_path = r'E:\\工资表\\工资表\\xlsx\\测试'\n",
    "missing_values = [\"n/a\", \"na\", \"——\"]\n",
    "def convert_to_date(date_str):\n",
    "    parts = date_str.split(\".\")\n",
    "    year = int(parts[0])\n",
    "    month = int(parts[1])\n",
    "    day = int(parts[2])\n",
    "    date_obj = date(year, month, day)\n",
    "    return date_obj\n",
    "def convert_cn_to_date(date_str):\n",
    "    year = int(date_str.split(\"年\")[0])\n",
    "    month = int(date_str.split(\"月\")[0].split(\"年\")[1])\n",
    "    day = int(date_str.split(\"日\")[0].split(\"月\")[1])\n",
    "    print(year)\n",
    "    print(month)\n",
    "    print(day)\n",
    "    return date(year, month, day)\n",
    "# 遍历当前文件内容\n",
    "normal_pd =pd.DataFrame()\n",
    "jt_pd = pd.DataFrame()\n",
    "wp_pd = pd.DataFrame()\n",
    "\n",
    "data_source = ''  # 数据来源\n",
    "company_name = ''  # 公司名称\n",
    "salary_grant_date = ''  # 工资发放时间\n",
    "salary_date = ''  # 工资时间\n",
    "\n",
    "for root, dirs, files in os.walk(target_path):\n",
    "    for file in files:\n",
    "        file_name = os.path.join(root, file)\n",
    "        print(file_name)\n",
    "        f = pd.ExcelFile(file_name)\n",
    "        # 获取文件名称\n",
    "        for i in f.sheet_names:\n",
    "            # 劵投名单\n",
    "            if name == \"券投\":\n",
    "                jtdf = pd.read_excel(file_name, sheet_name=name)\n",
    "                mydf = pd.read_excel(file_name, na_values=missing_values, header=2, sheet_name=name)\n",
    "                heads = jtdf.columns\n",
    "                head = jtdf.columns.values[0]\n",
    "                name = head.split(\"券投交易中心\")[1]\n",
    "                grant_salary_str = jtdf.iloc[:, 0].values[0].split(\"：\")[1]\n",
    "                company_name = '券投交易中心'\n",
    "                salary_grant_date = head\n",
    "                salary_date = convert_cn_to_date(grant_salary_str)\n",
    "                # 添加字段以及内容\n",
    "                mydf['数据来源'] = data_source\n",
    "                mydf['公司名称'] = company_name\n",
    "                mydf['工资发放时间'] = salary_grant_date\n",
    "                mydf['工资时间'] = salary_date\n",
    "                mydf['签名'] = mydf['签名'].fillna('')\n",
    "                mydf = mydf.fillna(0)\n",
    "                #display(mydf.loc[mydf['序号'] > 0])\n",
    "                jt_pd = pd.concat([jt_pd,mydf])\n",
    "                #display(normal_pd)\n",
    "                #jt_pd.append(mydf.loc[mydf['序号'] > 0])\n",
    "            # 外聘人员\n",
    "            if name == \"外聘人员\":\n",
    "                jtdf = pd.read_excel(file_name, sheet_name=name)\n",
    "                mydf = pd.read_excel(file_name, na_values=missing_values, header=1, sheet_name=name)\n",
    "                heads = jtdf.columns\n",
    "                head = jtdf.columns.values[0]\n",
    "                name = head.split(\"外聘人员\")[1]\n",
    "                company_name = '外聘人员'\n",
    "                salary_grant_date = head\n",
    "                mydf['数据来源'] = data_source\n",
    "                mydf['公司名称'] = company_name\n",
    "                mydf['工资发放时间'] = salary_grant_date\n",
    "                mydf['工资时间'] = ''\n",
    "                mydf = mydf.fillna(0)\n",
    "                wp_pd = pd.concat([wp_pd,mydf])\n",
    "                #display(mydf.loc[mydf['姓名'] != 0])\n",
    "                #wp_pd.append(mydf.loc[mydf['姓名'] != 0])\n",
    "            if name == '总表':\n",
    "                continue\n",
    "            else:\n",
    "                head_df = pd.read_excel(file_name, na_values=missing_values, sheet_name=i)\n",
    "                mydf = pd.read_excel(file_name, na_values=missing_values, header=1, sheet_name=i)\n",
    "                heads = head_df.columns\n",
    "                # print(heads)\n",
    "                # 确定内容额外字段的内容\n",
    "                head_arr = []\n",
    "                for head in heads:\n",
    "                    # print(type(head))\n",
    "                    if (head.find(\"公司\") != -1):\n",
    "                        company_name = head.split(\"公司\")[0] + \"公司\"\n",
    "                        salary_date = head.split(\"公司\")[1]\n",
    "                    if (head.find(\"日期\") != -1):\n",
    "                        date_str = head.split(\"：\")[1]\n",
    "                        salary_grant_date = convert_to_date(date_str)\n",
    "\n",
    "                # mydf.rename(社保扣除项=基本养老保险费)\n",
    "                # 不合法字段替换\n",
    "                mydf = mydf.rename(columns={'社保扣除项': '基本养老保险费'})\n",
    "                mydf = mydf.rename(columns={'Unnamed: 14': '基本医疗保险费'})\n",
    "                mydf = mydf.rename(columns={'Unnamed: 15': '失业保险费'})\n",
    "                mydf = mydf.rename(columns={'Unnamed: 16': '社保个人扣款合计'})\n",
    "                mydf['签名'] = mydf['签名'].fillna('')\n",
    "                mydf = mydf.fillna(0)\n",
    "                mydf = mydf.drop(index=0)\n",
    "                # 添加字段以及内容\n",
    "                mydf['数据来源'] = data_source\n",
    "                mydf['公司名称'] = company_name\n",
    "                mydf['工资发放时间'] = salary_grant_date\n",
    "                mydf['工资时间'] = salary_date\n",
    "                normal_pd = pd.concat([normal_pd,mydf])\n",
    "#display(jt_pd)\n",
    "#display(wp_pd)\n",
    "#display(normal_pd)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date, datetime\n",
    "import os\n",
    "\n",
    "target_path = r'E:\\工资表\\测试'\n",
    "missing_values = [\"n/a\", \"na\", \"——\"]\n",
    "def convert_to_date(date_str):\n",
    "    parts = date_str.split(\".\")\n",
    "    year = int(parts[0])\n",
    "    month = int(parts[1])\n",
    "    day = int(parts[2])\n",
    "    date_obj = date(year, month, day)\n",
    "    return date_obj\n",
    "def convert_cn_to_date(date_str):\n",
    "    year = int(date_str.split(\"年\")[0])\n",
    "    month = int(date_str.split(\"月\")[0].split(\"年\")[1])\n",
    "    day = int(date_str.split(\"日\")[0].split(\"月\")[1])\n",
    "    return date(year, month, day)\n",
    "# 遍历当前文件内容\n",
    "normal_pd =pd.DataFrame()\n",
    "jt_pd = pd.DataFrame()\n",
    "wp_pd = pd.DataFrame()\n",
    "\n",
    "company_name = ''  # 公司名称\n",
    "salary_grant_date = ''  # 工资发放时间\n",
    "salary_date = ''  # 工资时间\n",
    "for root, dirs, files in os.walk(target_path):\n",
    "    for file in files:\n",
    "        file_name = os.path.join(root, file)\n",
    "        data_source = file_name\n",
    "        print(file_name)\n",
    "        f = pd.ExcelFile(file_name)\n",
    "        # 获取文件名称\n",
    "        for name in f.sheet_names:\n",
    "            # 劵投名单\n",
    "            if name == \"券投\":\n",
    "                jtdf = pd.read_excel(file_name, sheet_name=name)\n",
    "                mydf1 = pd.read_excel(file_name, na_values=missing_values, header=2, sheet_name=name)\n",
    "                heads = jtdf.columns\n",
    "                head = jtdf.columns.values[0]\n",
    "                name = head.split(\"券投交易中心\")[1]\n",
    "                grant_salary_str = jtdf.iloc[:, 0].values[0].split(\"：\")[1]\n",
    "                company_name = '券投交易中心'\n",
    "                salary_grant_date = head\n",
    "                salary_date = convert_cn_to_date(grant_salary_str)\n",
    "                # 添加字段以及内容\n",
    "                mydf1['数据来源'] = data_source\n",
    "                mydf1['公司名称'] = company_name\n",
    "                mydf1['工资发放时间'] = salary_grant_date\n",
    "                mydf1['工资时间'] = salary_date\n",
    "                mydf1['签名'] = mydf1['签名'].fillna('')\n",
    "                mydf1 = mydf1.fillna(0)\n",
    "                mydf1['序号'] = mydf1['序号'].replace('总经理审批：',0)\n",
    "                mydf1 = mydf1.loc[mydf1['序号'].astype(int) >0]\n",
    "                \n",
    "                jt_pd = pd.concat([jt_pd,mydf1])\n",
    "            elif name == \"外聘人员\":\n",
    "                wpdf = pd.read_excel(file_name, sheet_name=name)\n",
    "                mydf2 = pd.read_excel(file_name, na_values=missing_values, header=1, sheet_name=name)\n",
    "                heads = wpdf.columns\n",
    "                head = wpdf.columns.values[0]\n",
    "                name = head.split(\"外聘人员\")[1]\n",
    "                company_name = '外聘人员'\n",
    "                salary_grant_date = head\n",
    "                mydf2['数据来源'] = data_source\n",
    "                mydf2['公司名称'] = company_name\n",
    "                mydf2['工资发放时间'] = salary_grant_date\n",
    "                mydf2['工资时间'] = ''\n",
    "                mydf2 = mydf2.fillna(0)\n",
    "                mydf2['序号'] = mydf2['序号'].replace('总经理审批：',0)\n",
    "                mydf2 = mydf2.loc[mydf2['序号'].astype(int) >0]\n",
    "                wp_pd = pd.concat([wp_pd,mydf2])\n",
    "                \n",
    "                #display(mydf.loc[mydf['姓名'] != 0])\n",
    "                #wp_pd.append(mydf.loc[mydf['姓名'] != 0])\n",
    "            elif name == '总表':\n",
    "                continue\n",
    "            else:\n",
    "                #print(name)\n",
    "                head_df = pd.read_excel(file_name, na_values=missing_values, sheet_name=name)\n",
    "                mydf3 = pd.read_excel(file_name, na_values=missing_values, header=1, sheet_name=name)\n",
    "                heads = head_df.columns\n",
    "                # print(heads)\n",
    "                # 确定内容额外字段的内容\n",
    "                for head in heads:\n",
    "                    # print(type(head))\n",
    "                    if (head.find(\"公司\") != -1):\n",
    "                        company_name = head.split(\"公司\")[0] + \"公司\"\n",
    "                        salary_date = head.split(\"公司\")[1]\n",
    "                    if (head.find(\"日期\") != -1):\n",
    "                        date_str = head.split(\"：\")[1]\n",
    "                        salary_grant_date = convert_to_date(date_str)\n",
    "\n",
    "                # mydf.rename(社保扣除项=基本养老保险费)\n",
    "                # 不合法字段替换\n",
    "                mydf3 = mydf3.rename(columns={'社保扣除项': '基本养老保险费'})\n",
    "                mydf3 = mydf3.rename(columns={'Unnamed: 14': '基本医疗保险费'})\n",
    "                mydf3 = mydf3.rename(columns={'Unnamed: 15': '失业保险费'})\n",
    "                mydf3 = mydf3.rename(columns={'Unnamed: 16': '社保个人扣款合计'})\n",
    "                mydf3['签名'] = mydf3['签名'].fillna('')\n",
    "                mydf3 = mydf3.fillna(0)\n",
    "                mydf3 = mydf3.drop(index=0)\n",
    "                # 添加字段以及内容\n",
    "                mydf3['数据来源'] = data_source\n",
    "                mydf3['公司名称'] = company_name\n",
    "                mydf3['工资发放时间'] = salary_grant_date\n",
    "                mydf3['工资时间'] = salary_date\n",
    "                mydf3['序号'] = mydf3['序号'].replace('合计',0)\n",
    "                mydf3 = mydf3.loc[mydf3['序号']>0]\n",
    "                normal_pd = pd.concat([normal_pd,mydf3])\n",
    "display(wp_pd)\n",
    "display(jt_pd)\n",
    "#normal_pd.to_excel('工资表测试.xlsx')\n",
    "#display(normal_pd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date, datetime\n",
    "import os\n",
    "\n",
    "target_path = r'E:\\工资表\\测试'\n",
    "missing_values = [\"n/a\", \"na\", \"——\"]\n",
    "def convert_to_date(date_str):\n",
    "    parts = date_str.split(\".\")\n",
    "    year = int(parts[0])\n",
    "    month = int(parts[1])\n",
    "    day = int(parts[2])\n",
    "    date_obj = date(year, month, day)\n",
    "    return date_obj\n",
    "def convert_cn_to_date(date_str):\n",
    "    year = int(date_str.split(\"年\")[0])\n",
    "    month = int(date_str.split(\"月\")[0].split(\"年\")[1])\n",
    "    day = int(date_str.split(\"日\")[0].split(\"月\")[1])\n",
    "    return date(year, month, day)\n",
    "# 遍历当前文件内容\n",
    "normal_pd =pd.DataFrame()\n",
    "jt_pd = pd.DataFrame()\n",
    "wp_pd = pd.DataFrame()\n",
    "\n",
    "company_name = ''  # 公司名称\n",
    "salary_grant_date = ''  # 工资发放时间\n",
    "salary_date = ''  # 工资时间\n",
    "for root, dirs, files in os.walk(target_path):\n",
    "    for file in files:\n",
    "        file_name = os.path.join(root, file)\n",
    "        data_source = file_name\n",
    "        print(file_name)\n",
    "        f = pd.ExcelFile(file_name)\n",
    "        # 获取文件名称\n",
    "        for name in f.sheet_names:\n",
    "            # 劵投名单\n",
    "            if name == \"券投\":\n",
    "                jtdf = pd.read_excel(file_name, sheet_name=name)\n",
    "                mydf1 = pd.read_excel(file_name, na_values=missing_values, header=2, sheet_name=name)\n",
    "                heads = jtdf.columns\n",
    "                head = jtdf.columns.values[0]\n",
    "                name = head.split(\"券投交易中心\")[1]\n",
    "                grant_salary_str = jtdf.iloc[:, 0].values[0].split(\"：\")[1]\n",
    "                company_name = '券投交易中心'\n",
    "                salary_grant_date = head\n",
    "                salary_date = convert_cn_to_date(grant_salary_str)\n",
    "                # 添加字段以及内容\n",
    "                mydf1['数据来源'] = data_source\n",
    "                mydf1['公司名称'] = company_name\n",
    "                mydf1['工资发放时间'] = salary_grant_date\n",
    "                mydf1['工资时间'] = salary_date\n",
    "                mydf1['签名'] = mydf1['签名'].fillna('')\n",
    "                mydf1 = mydf1.fillna(0)\n",
    "                display(mydf1.loc[mydf1['序号'].astype(int) >0])\n",
    "                jt_pd = pd.concat([jt_pd,mydf1])\n",
    "#display(wp_pd)\n",
    "#display(jt_pd) \n",
    "#normal_pd.to_excel('工资表测试.xlsx')\n",
    "#display(normal_pd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
